package com.yellowpage.client.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.yellowpage.client.data.UserInfo;
import com.yellowpage.nsp.dao.RemoteNspUserDAO;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.BaseDaoHibernateImplExtend;
import com.yellowpage.white.dao.QueryAssistantSQL;

public class UserInfoDaoImpl extends BaseDaoHibernateImplExtend<UserInfo, Long> implements UserInfoDao {
	
	private final static Properties properties = new Properties();
	private static Log logger = LogFactory.getLog(RemoteNspUserDAO.class);
	
	String nsp_db_driver;
	String nsp_db_url;
	String nsp_db_username;
	String nsp_db_password;

	public UserInfoDaoImpl() {
		super(UserInfo.class);
		
		try {			
			properties.load(this.getClass().getClassLoader().getResourceAsStream("jdbc.properties"));  

		} catch (Exception e) {
			e.printStackTrace();
		}
		
		nsp_db_driver = properties.getProperty("nsp_client_db_driver");
		nsp_db_url = properties.getProperty("nsp_client_db_url");
		nsp_db_username = properties.getProperty("nsp_client_db_username");
		nsp_db_password = properties.getProperty("nsp_client_db_password");

/*		nsp_db_driver = properties.getProperty("jdbc.driverClassName");
		nsp_db_url = properties.getProperty("jdbc.url");
		nsp_db_username = properties.getProperty("jdbc.username");
		nsp_db_password = properties.getProperty("jdbc.password");*/
	}		
	
	
    public List<Map> queryUserInfoByPage(Map queryMap, PageRender pageRender) {		
    	Connection nspConn = null;
    	
    	nspConn = getNSPConnection();
    	
    	String sql = "SELECT a.muid , a.cust_id , a.username , a.esn , a.phone_categ , a.phone , a.imsi , " +
		"       a.classid , a.level_id , a.telphone , a.email , a.sex , a.birthday , a.pno , " +
		"       a.province , a.city , a.district , a.adress , a.add_channel , a.add_time , a.tel_integral , " +
		"       a.sl_integral , a.balance , a.last_login , a.last_ip , a.seqno , a.mac_key , " +
		"       a.certificatetype , a.certificatecode , a.field1 , a.field2 , a.field3 , a.name , a.id , a.type , " +
		"       a.version , a.password , a.status  " +
		"  FROM user_info a WHERE 1=1 ";
    		
//    		if(queryMap.get("name") != null 
//    				&& ((String)queryMap.get("name")).length() > 0){
//    			sql += " and name = '" + queryMap.get("name") + "' ";
//    		}
        
		QueryAssistantSQL sqlAssistant = this.newQueryAssistantSQL(
				sql)
				    			.addCondition(" and muid = :muid ")
							    			.addCondition(" and cust_id = :custId ")
							    			.addCondition(" and username = :username ")
							    			.addCondition(" and esn = :esn ")
							    			.addCondition(" and phone_categ = :phoneCateg ")
							    			.addCondition(" and phone = :phone ")
							    			.addCondition(" and imsi = :imsi ")
							    			.addCondition(" and classid = :classid ")
							    			.addCondition(" and level_id = :levelId ")
							    			.addCondition(" and telphone = :telphone ")
							    			.addCondition(" and email = :email ")
							    			.addCondition(" and sex = :sex ")
							    		    .addCondition(" and date_format(birthday,'%Y-%m-%d') =  :birthday  ")
		    				    			.addCondition(" and pno = :pno ")
							    			.addCondition(" and province = :province ")
							    			.addCondition(" and city = :city ")
							    			.addCondition(" and district = :district ")
							    			.addCondition(" and adress = :adress ")
							    			.addCondition(" and add_channel = :addChannel ")
							    			.addCondition(" and date_format(add_time,'%Y-%m-%d') >= :addTime ")
							    			.addCondition(" and date_format(add_time,'%Y-%m-%d') <= :addTimeend ")
							    			.addCondition(" and tel_integral = :telIntegral ")
							    			.addCondition(" and sl_integral = :slIntegral ")
							    			.addCondition(" and balance = :balance ")
							    			.addCondition(" and date_format(last_login,'%Y-%m-%d') >= :lastLogin ")
							    			.addCondition(" and date_format(last_login,'%Y-%m-%d') <= :lastLoginend ")
							    			.addCondition(" and last_ip = :lastIp ")
							    			.addCondition(" and seqno = :seqno ")
							    			.addCondition(" and mac_key = :macKey ")
							    			.addCondition(" and certificatetype = :certificatetype ")
							    			.addCondition(" and certificatecode = :certificatecode ")
							    			.addCondition(" and field1 = :field1 ")
							    			.addCondition(" and field2 = :field2 ")
							    			.addCondition(" and field3 = :field3 ")
							    			.addCondition(" and name = :name ")
							    			.addCondition(" and id = :id ")
							    			.addCondition(" and type = :type ")
							    			.addCondition(" and version = :version ")
							    			.addCondition(" and password = :password ")
							    			.addCondition(" and status = :status ")
							    ; 		    
		    

		List<Map> resultList = sqlAssistant.queryByPage(
				nspConn, queryMap, pageRender);

		return resultList;
	}
    public List<Map> queryUserInfo(Map queryMap) {		
    	Connection nspConn = null;
    	
    	nspConn = getNSPConnection();
    	
    	String sql = "SELECT a.muid , a.cust_id , a.username , a.esn , a.phone_categ , a.phone , a.imsi , " +
		"       a.classid , a.level_id , a.telphone , a.email , a.sex , a.birthday , a.pno , " +
		"       a.province , a.city , a.district , a.adress , a.add_channel , a.add_time , a.tel_integral , " +
		"       a.sl_integral , a.balance , a.last_login , a.last_ip , a.seqno , a.mac_key , " +
		"       a.certificatetype , a.certificatecode , a.field1 , a.field2 , a.field3 , a.name , a.id , a.type , " +
		"       a.version , a.password , a.status  " +
		"  FROM user_info a WHERE 1=1 ";
    		
//    		if(queryMap.get("name") != null 
//    				&& ((String)queryMap.get("name")).length() > 0){
//    			sql += " and name = '" + queryMap.get("name") + "' ";
//    		}
        
		QueryAssistantSQL sqlAssistant = this.newQueryAssistantSQL(
				sql)
				    			.addCondition(" and muid = :muid ")
							    			.addCondition(" and cust_id = :custId ")
							    			.addCondition(" and username = :username ")
							    			.addCondition(" and esn = :esn ")
							    			.addCondition(" and phone_categ = :phoneCateg ")
							    			.addCondition(" and phone = :phone ")
							    			.addCondition(" and imsi = :imsi ")
							    			.addCondition(" and classid = :classid ")
							    			.addCondition(" and level_id = :levelId ")
							    			.addCondition(" and telphone = :telphone ")
							    			.addCondition(" and email = :email ")
							    			.addCondition(" and sex = :sex ")
							    		    .addCondition(" and date_format(birthday,'%Y-%m-%d') =  :birthday  ")
		    				    			.addCondition(" and pno = :pno ")
							    			.addCondition(" and province = :province ")
							    			.addCondition(" and city = :city ")
							    			.addCondition(" and district = :district ")
							    			.addCondition(" and adress = :adress ")
							    			.addCondition(" and add_channel = :addChannel ")
							    			.addCondition(" and date_format(add_time,'%Y-%m-%d') >= :addTime ")
							    			.addCondition(" and date_format(add_time,'%Y-%m-%d') <= :addTimeend ")
							    			.addCondition(" and tel_integral = :telIntegral ")
							    			.addCondition(" and sl_integral = :slIntegral ")
							    			.addCondition(" and balance = :balance ")
							    			.addCondition(" and date_format(last_login,'%Y-%m-%d') >= :lastLogin ")
							    			.addCondition(" and date_format(last_login,'%Y-%m-%d') <= :lastLoginend ")
							    			.addCondition(" and last_ip = :lastIp ")
							    			.addCondition(" and seqno = :seqno ")
							    			.addCondition(" and mac_key = :macKey ")
							    			.addCondition(" and certificatetype = :certificatetype ")
							    			.addCondition(" and certificatecode = :certificatecode ")
							    			.addCondition(" and field1 = :field1 ")
							    			.addCondition(" and field2 = :field2 ")
							    			.addCondition(" and field3 = :field3 ")
							    			.addCondition(" and name = :name ")
							    			.addCondition(" and id = :id ")
							    			.addCondition(" and type = :type ")
							    			.addCondition(" and version = :version ")
							    			.addCondition(" and password = :password ")
							    			.addCondition(" and status = :status ")
							    ; 		    
		    

		List<Map> resultList = sqlAssistant.query(
				nspConn, queryMap);

		return resultList;
	}
    
    private Connection getNSPConnection(){
        try {

            Class.forName(nsp_db_driver);
            
            logger.error("nsp_db_url:" + nsp_db_url);
            logger.error("nsp_db_username:" + nsp_db_username);
            logger.error("nsp_db_password:" + nsp_db_password);
            logger.error("nsp_db_driver:" + nsp_db_driver);
        
            Connection connection = DriverManager.getConnection(nsp_db_url, nsp_db_username, nsp_db_password);

            return connection;

        } catch (Exception e) {
            logger.error(e);
        }
        return null;
    }
		

}	